﻿Imports System.Data.OleDb
'Imports System.Data.
Public Class funcionesExcel
    Private nombreArchivo As String

    Public Sub New(ByVal sNombreArchivo As String)
        nombreArchivo = sNombreArchivo
    End Sub

    Public Function obtenerHojasExcel() As List(Of String)
        Dim objOleConnection As System.Data.OleDb.OleDbConnection

        ' // Comprobar que el archivo Excel existe  
        If Not (System.IO.File.Exists(nombreArchivo)) Then
            MsgBox("No se ha encontrado el archivo: " & nombreArchivo, MsgBoxStyle.Exclamation)
            Return Nothing
        End If

        ' // Declarar la Cadena de conexión  
        Dim sCs As String = ""
        If nombreArchivo.Substring(nombreArchivo.Length - 4) = "xlsx" Then
            'Archivos de Excel 2007 - 2010
            sCs = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & nombreArchivo & ";Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1;'"
        Else
            'Archivos de Excel 93 a 2003
            sCs = "provider=Microsoft.Jet.OLEDB.4.0; data source=" & nombreArchivo & "; Extended Properties=Excel 8.0;"
        End If
        objOleConnection = New System.Data.OleDb.OleDbConnection(sCs)
        Try
            objOleConnection.Open()

            'Obtenemos todas las hojas
            Dim objDataSet As System.Data.DataTable
            objDataSet = objOleConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, Nothing)

            'Dim i As Integer = 0
            Dim x As String
            Dim lista = New List(Of String)

            For i As Integer = 0 To objDataSet.Rows.Count - 1
                x = objDataSet.Rows(i).Item("TABLE_NAME").ToString
                If x.Substring(x.Length - 1, 1).Equals("$") Then
                    lista.Add(x.Substring(0, x.Length - 1))
                End If
            Next

            Return lista
        Catch ex As Exception
            MsgBox("Problemas al obtener hojas :" + ex.Message)
            Return Nothing
        Finally
            objOleConnection.Close()
        End Try
        Return Nothing

    End Function

    Public Function cargarExcelDatagrid(ByVal sNombreArchivo As String, ByVal sNombreHoja As String, ByVal sRango As String) As DataSet
        Try
            ' // Comprobar que el archivo Excel existe  
            If System.IO.File.Exists(sNombreArchivo) Then
                Dim objDataSet As System.Data.DataSet
                Dim objDataAdapter As System.Data.OleDb.OleDbDataAdapter
                ' // Declarar la Cadena de conexión  

                Dim sCs As String = ""
                If sNombreArchivo.Substring(sNombreArchivo.Length - 4) = "xlsx" Then
                    'Archivos de Excel 2007 - 2013
                    sCs = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & sNombreArchivo & ";Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1;'"
                Else
                    'Archivos de Excel 93 a 2003
                    sCs = "provider=Microsoft.Jet.OLEDB.4.0; data source=" & sNombreArchivo & "; Extended Properties=Excel 8.0;"
                End If


                Dim objOleConnection = New System.Data.OleDb.OleDbConnection(sCs)
                'objOleConnection = New System.Data.OleDb.OleDbConnection(sCs)

                ' // Declarar la consulta SQL que indica el libro y el rango de la hoja  
                Dim sSql As String = "select * from " & "[" & sNombreHoja & "$" & sRango & "]"
                ' // Obtener los datos  
                objDataAdapter = New System.Data.OleDb.OleDbDataAdapter(sSql, objOleConnection)

                ' // Crear DataSet y llenarlo  
                objDataSet = New System.Data.DataSet

                objDataAdapter.Fill(objDataSet)
                ' // Cerrar la conexión  
                objOleConnection.Close()

                Return objDataSet
            Else
                MsgBox("No se ha encontrado el archivo: " & sNombreArchivo, MsgBoxStyle.Exclamation)
                Return Nothing
            End If

            Exit Function
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function
End Class
